/***********************************************************************************************************************************
 * Name:	Trace_PerformanceOverview.sql
 * Author:	Frank Figearo (frank@sqlnerd.me)
 * Summary:	Template for new query windows.
**/

USE master;
DECLARE @TraceFile		NVARCHAR(245);	SET @TraceFile	= DBAdmin.GetInstancePath(N'ErrorDumpDir') + N'PerformanceOverview'; PRINT @TraceFile;
DECLARE @StopTime		DATETIME;		SET @StopTime	= DATEADD(hh,1,GETDATE());	-- 1hr Default
DECLARE @MaxFileSize	BIGINT;			SET @MaxFileSize= 128;
DECLARE @On				BIT;			SET @On			= 1;
DECLARE @TraceID		INT;	-- Trace ID created by sp_trace_create
DECLARE @EventID		INT;
DECLARE @ReturnValue	INT;

-- create the trace ================================================================================================================
EXECUTE @ReturnValue= dbo.sp_trace_create @TraceID = @TraceID OUTPUT
	, @options		= 2				-- TRACE_FILE_ROLLOVER
	, @tracefile	= @TraceFile
	, @maxfilesize	= @MaxFileSize
	, @stoptime		= @StopTime
	, @filecount	= 8;
/*
 *	Return	Description
 *	0		No error.
 *	1		Unknown error.
 *	10		Invalid options. Returned when options specified are incompatible.
 *	12		File not created.
 *	13		Out of memory. Returned when there is not enough memory to perform the specified action.
 *	14		Invalid stop time. Returned when the stop time specified has already happened.
 *	15		Invalid parameters. Returned when the user supplied incompatible parameters.
 */
SELECT @TraceID AS [Trace ID], @ReturnValue AS [Return Value];

-- set the events ==================================================================================================================
DECLARE @Events TABLE (EventID INT NOT NULL PRIMARY KEY CLUSTERED);
INSERT INTO	@Events (EventID) VALUES (10)	-- RPC:Completed		Occurs when a remote procedure call (RPC) has completed.
INSERT INTO	@Events (EventID) VALUES (12)	-- SQL:BatchCompleted	Occurs when a Transact-SQL batch has completed.

DECLARE list CURSOR LOCAL FAST_FORWARD FOR SELECT EventID FROM @Events;
OPEN list;
WHILE 0=0 BEGIN
	FETCH NEXT FROM list INTO @EventID;
	IF @@FETCH_STATUS <> 0 BREAK;
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID,  1, @On;	-- TextData
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID,  8, @On;	-- HostName
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 10, @On;	-- ApplicationName
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 11, @On;	-- LoginName 
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 12, @On;	-- SPID 
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 13, @On;	-- Duration
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 14, @On;	-- StartTime
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 15, @On;	-- EndTime
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 16, @On;	-- Reads
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 17, @On;	-- Writes
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 18, @On;	-- CPU
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 35, @On;	-- DatabaseName
	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 51, @On;	-- EventSequence
--	EXECUTE dbo.sp_trace_setevent @TraceID, @EventID, 63, @On;	-- SQLHandle
END;
CLOSE list;
DEALLOCATE list;


/* filter ==========================================================================================================================
 *	@logical_operator
 *		0=AND
 *		1=OR
 *	@comparison_operator
 *		 0	=  (Equal)
 *		 1	<> (Not Equal)
 *		 2	>  (Greater Than)
 *		 3	<  (Less Than)
 *		 4	>= (Greater Than or Equal)
 *		 5	<= (Less Than or Equal)
 *		 6	LIKE
 *		 7	NOT LIKE
 *	EXECUTE @ReturnValue= dbo.sp_trace_setfilter @TraceID, @columnid, @logical_operator, @comparison_operator, @value;
 */
DECLARE @BigZero BIGINT= 0;
EXECUTE dbo.sp_trace_setfilter 2, 13, 1, 2, @BigZero;	-- 0 < Duration
EXECUTE dbo.sp_trace_setfilter 2, 16, 1, 2, @BigZero;	-- 0 < Reads
EXECUTE dbo.sp_trace_setfilter 2, 17, 1, 2, @BigZero;	-- 0 < Writes
EXECUTE dbo.sp_trace_setfilter 2, 18, 1, 2, 0;			-- 0 < CPU


-- start the trace
EXECUTE sp_trace_setstatus
	@TraceID= @TraceID,
	@status	= 1;
--0	Stops the specified trace.
--1	Starts the specified trace.
--2	Closes the specified trace and deletes its definition from the server.

/* check trace status
 */
DECLARE @property TABLE (PropertyID INT NOT Null PRIMARY KEY CLUSTERED, PropertyText VARCHAR(9) NOT Null);
INSERT INTO @property VALUES (1,'Options');		--	1 = Trace Options. For more information, see @options in sp_trace_create (Transact-SQL). 
INSERT INTO @property VALUES (2,'File Name');	--	2 = File Name 
INSERT INTO @property VALUES (3,'Max Size');	--	3 = Max Size 
INSERT INTO @property VALUES (4,'Stop Time');	--	4 = Stop Time 
INSERT INTO @property VALUES (5,'Status');		--	5 = Current Status: 0 = stopped; 1 = running.
SELECT TraceID, PropertyText AS Property, Value FROM fn_trace_getinfo(default) i INNER JOIN @property p ON i.Property = p.PropertyID;
GO


/***********************************************************************************************************************************
 * Load Trace Data for Analysis
 */
:SETVAR TableName ##PerformanceOverview
PRINT N'$(TableName)'
CREATE TABLE [$(TableName)]	(
	TextData		NVARCHAR(MAX)		Null,
	DatabaseID		INT					Null,
	HostName		NVARCHAR(256)		Null,
	ApplicationName	NVARCHAR(256)		Null,
	LoginName		NVARCHAR(256)		Null,
	SPID			INT					Null,
	Duration		BIGINT				Null,
	StartTime		DATETIME2(3)		Null,
	EndTime			DATETIME2(3)		Null,
	Reads			BIGINT				Null,
	Writes			BIGINT				Null,
	CPU				INT					Null,
	ServerName		NVARCHAR(256)		Null,
	EventClass		INT					Null,
	DatabaseName	NVARCHAR(256)		NULL,
	EventSequence	INT					Null,
	SQLHandle		BIGINT				Null);

DECLARE @TraceFile NVARCHAR(245); SET @TraceFile= master.DBAdmin.GetInstancePath(N'ErrorDumpDir') + N'PerformanceOverview.trc';
INSERT INTO [$(TableName)] (TextData, DatabaseID, HostName, ApplicationName, LoginName, SPID, Duration, StartTime, EndTime, Reads, Writes, CPU, ServerName, EventClass, DatabaseName, EventSequence, SQLHandle)
	SELECT                  TextData, DatabaseID, HostName, ApplicationName, LoginName, SPID, Duration, StartTime, EndTime, Reads, Writes, CPU, ServerName, EventClass, DatabaseName, EventSequence, SQLHandle
	  FROM sys.fn_trace_gettable(@TraceFile, default);
GO